﻿CREATE PROCEDURE [dbo].[AssortmentInsert]
(
	@CatalogId int,
	@Name varchar(150),
	@MedFormId int,
	@AssesmentGroupId int,
	@ABList char(1),
	@NotPatentedRus varchar(100),
	@NotPatentedLat varchar(100),
	@GNVLS bit,
	@Mandatory bit,
	@StoreTemperature varchar(20),
	@BeforeOverdue tinyint,
	@NoRecipe bit,
	@ReferentPrice decimal(19, 2),
	@ReferentDate smalldatetime,
	@Note varchar(250),
	@MD datetime,
	@MW varchar(50)
)
AS
DECLARE @strMsg nvarchar(500), @intRet int, @intErrCode int
DECLARE @intNextId int, @bitRefToDelete bit
SET NOCOUNT ON

IF (@Name Is Null) OR (@MedFormId Is Null) OR (@CatalogId Is Null)
BEGIN
	SELECT @intErrCode = 1, @strMsg = 'Неправильные параметры в вызове процедуры ' + Object_Name(@@Procid) + ' !'
	RAISERROR (@strMsg, 11, @intErrCode)
	RETURN @intErrCode
END

IF ltrim(@Name) = ''
BEGIN
	SELECT @intErrCode = 2, @strMsg = 'Незаполнено наименование!'
	RAISERROR (@strMsg, 11, @intErrCode)
	RETURN @intErrCode
END

IF EXISTS (SELECT TOP 1 ID FROM [CA].[Assortment] WHERE Name = @Name AND MedFormId = @MedFormId AND ToDelete = 0) 
BEGIN
	SELECT @intErrCode = 3, @strMsg = 'Дублируется наименование и лекарственная форма!'
	RAISERROR (@strMsg, 11, @intErrCode)
	RETURN @intErrCode
END

IF NOT EXISTS (SELECT DepartmentId FROM PR.DepartmentOptions WHERE DepartmentId = 0 ) 
BEGIN
	SELECT @intErrCode = 4, @strMsg = 'Изменять данные спрвочников можно только в офисе!'
	RAISERROR (@strMsg, 11, @intErrCode)
	RETURN @intErrCode
END

SELECT @bitRefToDelete = ToDelete FROM CO.Catalog WHERE Id = @CatalogId
IF @@rowcount = 0
BEGIN
	SELECT @intErrCode = 5, 
	@strMsg = 'Отсутствует группа с идентификатором ' + cast(@CatalogId as varchar(20)) + '!'
	RAISERROR (@strMsg, 11, @intErrCode)
	RETURN @intErrCode
END
IF @bitRefToDelete = 1
BEGIN
	SELECT @intErrCode = 6, 
	@strMsg = 'Группа с идентификатором ' + cast(@CatalogId as varchar(20)) + ' отмечена для удаления!'
	RAISERROR (@strMsg, 11, @intErrCode)
	RETURN @intErrCode
END

SELECT @bitRefToDelete = ToDelete FROM CO.MedForm WHERE Id = @MedFormId
IF @@rowcount = 0
BEGIN
	SELECT @intErrCode = 7, 
	@strMsg = 'Отсутствует медицинская форма с идентификатором ' + cast(@MedFormId as varchar(20)) + '!'
	RAISERROR (@strMsg, 11, @intErrCode)
	RETURN @intErrCode
END
IF @bitRefToDelete = 1
BEGIN
	SELECT @intErrCode = 8, 
	@strMsg = 'Медицинская форма с идентификатором ' + cast(@MedFormId as varchar(20)) + ' отмечена для удаления!'
	RAISERROR (@strMsg, 11, @intErrCode)
	RETURN @intErrCode
END

IF @AssesmentGroupId Is Not Null
BEGIN
	SELECT @bitRefToDelete = ToDelete FROM CO.AssessmentGroup WHERE Id = @AssesmentGroupId
	IF @@rowcount = 0
	BEGIN
		SELECT @intErrCode = 9, 
		@strMsg = 'Отсутствует группа учета с идентификатором ' + cast(@AssesmentGroupId as varchar(20)) + '!'
		RAISERROR (@strMsg, 11, @intErrCode)
		RETURN @intErrCode
	END
	IF @bitRefToDelete = 1
	BEGIN
		SELECT @intErrCode = 10, 
		@strMsg = 'Группа учета с идентификатором ' + cast(@AssesmentGroupId as varchar(20)) + ' отмечена для удаления!'
		RAISERROR (@strMsg, 11, @intErrCode)
		RETURN @intErrCode
	END
END

EXEC [dbo].[ProcessStatistic] @Note OUTPUT, @MD OUTPUT, @MW OUTPUT
--получение значения идентификатора
EXEC @intRet = dbo.GetNextCounterFor 'Common', @intNextId OUTPUT
IF @intRet <> 0 RETURN @intRet

	SET NOCOUNT OFF;
INSERT INTO [CA].[Assortment] ( Id, [CatalogId], [Name], [MedFormId], [AssesmentGroupId], [ABList], [NotPatentedRus], [NotPatentedLat], [GNVLS], [Mandatory], [StoreTemperature], [BeforeOverdue], [NoRecipe], [ReferentPrice], [ReferentDate], [Note], [MD], [MW], ToDelete) 
VALUES ( @intNextId, @CatalogId, @Name, @MedFormId, @AssesmentGroupId, @ABList, @NotPatentedRus, @NotPatentedLat, @GNVLS, @Mandatory, @StoreTemperature, @BeforeOverdue, @NoRecipe, @ReferentPrice, @ReferentDate, @Note, @MD, @MW, 0);
	
SELECT Id, GId, CatalogId, Name, MedFormId, AssesmentGroupId, ABList, NotPatentedRus, NotPatentedLat, GNVLS, Mandatory, StoreTemperature, BeforeOverdue, NoRecipe, ReferentPrice, ReferentDate, Note, MD, MW, ToDelete
FROM CA.Assortment WHERE (Id = @intNextId)
RETURN 0

